Overview
GAStech is a company that is located in a country island of Kronos and it has come to their attention that some of the employees had mysteriously went missing. Vehicles tracking data that was secretly installed in the company’s cars and Kronos-Kares benefit card information are delivered to authorities for investigation.
Literature review of existing analysis performed
3. Extracting, wrangling and preparing the input data
3.1 Setting up environment
packages = c('tidyverse', 'lubridate', 'dplyr', 'raster', 'clock', 'sf', 'tmap', 'plotly', 'ggplot2', 'mapview', 'rgdal',
'rgeos', 'tidyr')
for (p in packages) {
if (!require(p, character.only = T)) {
install.packages(p, repos = "http://cran.us.r-project.org")
}
library(p, character.only = T)
}
Importing Employee’s Info and Car Assignment
carAssignment <- read_csv("mc2/car-assignments.csv")
carAssignment
# A tibble: 44 x 5
LastName FirstName CarID CurrentEmploymentT… CurrentEmploymentT…
<chr> <chr> <dbl> <chr> <chr>
1 Calixto Nils 1 Information Techno… IT Helpdesk
2 Azada Lars 2 Engineering Engineer
3 Balas Felix 3 Engineering Engineer
4 Barranco Ingrid 4 Executive SVP/CFO
5 Baza Isak 5 Information Techno… IT Technician
6 Bergen Linnea 6 Information Techno… IT Group Manager
7 Orilla Elsa 7 Engineering Drill Technician
8 Alcazar Lucas 8 Information Techno… IT Technician
9 Cazar Gustav 9 Engineering Drill Technician
10 Campo-Corr… Ada 10 Executive SVP/CIO
# … with 34 more rowsImport Credit card and Loyalty card data
ccData <- read_csv("MC2/cc_data.csv")
ccData$timestamp = date_time_parse(ccData$timestamp, zone = "", format = "%m/%d/%Y %H:%M")
ccData <- ccData %>%
mutate(date = as.Date(timestamp), time = strftime(timestamp, "%H:%M"), hr = strftime(timestamp, "%H"))
loyaltyData <- read_csv("MC2/loyalty_data.csv") %>%
mutate(date = as.Date(mdy(timestamp)))
ccLoyalty <- left_join(ccData, loyaltyData, by = c("date", "location", "price")) %>%
dplyr::select(date, time, location, price, last4ccnum, loyaltynum, hr) %>%
group_by(last4ccnum)
ccLoyalty$weekday = wday(ccLoyalty$date, label = TRUE, abbr = TRUE)
ccLoyalty$last4ccnum = as.character(ccLoyalty$last4ccnum)
ccLoyalty
# A tibble: 1,496 x 8
# Groups: last4ccnum [55]
date time location price last4ccnum loyaltynum hr weekday
<date> <chr> <chr> <dbl> <chr> <chr> <chr> <ord>
1 2014-01-05 07:28 Brew've… 11.3 4795 <NA> 07 Sun
2 2014-01-05 07:34 Hallowe… 52.2 7108 <NA> 07 Sun
3 2014-01-05 07:35 Brew've… 8.33 6816 <NA> 07 Sun
4 2014-01-05 07:36 Hallowe… 16.7 9617 <NA> 07 Sun
5 2014-01-05 07:37 Brew've… 4.24 7384 <NA> 07 Sun
6 2014-01-05 07:38 Brew've… 4.17 5368 <NA> 07 Sun
7 2014-01-05 07:42 Coffee … 28.7 7253 <NA> 07 Sun
8 2014-01-05 07:43 Brew've… 9.6 4948 <NA> 07 Sun
9 2014-01-05 07:43 Brew've… 16.9 9683 <NA> 07 Sun
10 2014-01-05 07:47 Hallowe… 16.5 8129 <NA> 07 Sun
# … with 1,486 more rows# A tibble: 64 x 2
# Groups: last4ccnum [55]
last4ccnum loyaltynum
<chr> <chr>
1 1286 L3572
2 1286 L3288
3 1310 L8012
4 1321 L4149
5 1415 L7783
6 1874 L4424
7 1877 L3014
8 2142 L9637
9 2276 L3317
10 2418 L9018
# … with 54 more rows# A tibble: 64 x 3
# Groups: last4ccnum [55]
last4ccnum loyaltynum ccPerson
<chr> <chr> <int>
1 1286 L3572 1
2 1286 L3288 1
3 1310 L8012 2
4 1321 L4149 3
5 1415 L7783 4
6 1874 L4424 5
7 1877 L3014 6
8 2142 L9637 7
9 2276 L3317 8
10 2418 L9018 9
# … with 54 more rows loyaltynum last4ccnum.x ccPerson last4ccnum.y
1 L3288 1286 1 9241
2 L3288 1286 1 1286
3 L3572 1286 1 1286
4 L8012 1310 2 1310
5 L4149 1321 3 1321
6 L7783 1415 4 1415
7 L4424 1874 5 1874
8 L3014 1877 6 1877
9 L9637 2142 7 2142
10 L3317 2276 8 2276
11 L9018 2418 9 2418
12 L6886 2463 10 2463
13 L5947 2540 11 2540
14 L1107 2681 12 2681
15 L2490 3484 13 3484
16 L7814 3492 14 3492
17 L7761 3506 15 3506
18 L9362 3547 16 3547
19 L1485 3853 17 3853
20 L2169 4434 18 4434
21 L8477 4530 19 4530
22 L2070 4795 20 4795
23 L2070 4795 20 8332
24 L8566 4795 20 8332
25 L8566 4795 20 4795
26 L3295 4948 21 5921
27 L3295 4948 21 4948
28 L9406 4948 21 5921
29 L9406 4948 21 8202
30 L9406 4948 21 4948
31 L2459 5010 22 5010
32 L2247 5368 23 7889
33 L2247 5368 23 5368
34 L6119 5368 23 5368
35 L6119 5368 23 7889
36 L4034 5407 24 5407
37 L3295 5921 25 5921
38 L3295 5921 25 4948
39 L9406 5921 25 5921
40 L9406 5921 25 8202
41 L9406 5921 25 4948
42 L6267 6691 26 6899
43 L6267 6691 26 6691
44 L8148 6816 27 6816
45 L3366 6895 28 6895
46 L6267 6899 29 6899
47 L6267 6899 29 6691
48 L9363 6901 30 6901
49 L6544 7108 31 7108
50 L6417 7117 32 7117
51 L1682 7253 33 7253
52 L6110 7354 34 7354
53 L6110 7354 34 8411
54 L9254 7354 34 7354
55 L3800 7384 35 7384
56 L4164 7688 36 7688
57 L5756 7792 37 7792
58 L5259 7819 38 7819
59 L2247 7889 39 7889
60 L2247 7889 39 5368
61 L6119 7889 39 5368
62 L6119 7889 39 7889
63 L8328 8129 40 8129
64 L5224 8156 41 8156
65 L2343 8202 42 8202
66 L9406 8202 42 5921
67 L9406 8202 42 8202
68 L9406 8202 42 4948
69 L2070 8332 43 4795
70 L2070 8332 43 8332
71 L8566 8332 43 8332
72 L8566 8332 43 4795
73 L6110 8411 44 7354
74 L6110 8411 44 8411
75 L2769 8642 45 8642
76 L5485 9152 46 9152
77 L4063 9220 47 9220
78 L3288 9241 48 9241
79 L3288 9241 48 1286
80 L3259 9405 49 9405
81 L5777 9551 50 9551
82 L5924 9614 51 9614
83 L5553 9617 52 9617
84 L3191 9635 53 9635
85 L7291 9683 54 9683
86 L9633 9735 55 9735 ccPerson last4ccnum.y
1 1 9241
2 1 1286
3 2 1310
4 3 1321
5 4 1415
6 5 1874
7 6 1877
8 7 2142
9 8 2276
10 9 2418
11 10 2463
12 11 2540
13 12 2681
14 13 3484
15 14 3492
16 15 3506
17 16 3547
18 17 3853
19 18 4434
20 19 4530
21 20 4795
22 20 8332
23 21 5921
24 21 4948
25 21 8202
26 22 5010
27 23 7889
28 23 5368
29 24 5407
30 26 6899
31 26 6691
32 27 6816
33 28 6895
34 30 6901
35 31 7108
36 32 7117
37 33 7253
38 34 7354
39 34 8411
40 35 7384
41 36 7688
42 37 7792
43 38 7819
44 40 8129
45 41 8156
46 45 8642
47 46 9152
48 47 9220
49 49 9405
50 50 9551
51 51 9614
52 52 9617
53 53 9635
54 54 9683
55 55 9735 loyaltynum last4ccnum.x ccPerson last4ccnum.y
1 L3288 1286 1 9241
2 L3288 1286 1 1286
3 L3572 1286 1 1286
4 L8012 1310 2 1310
5 L4149 1321 3 1321
6 L7783 1415 4 1415
7 L4424 1874 5 1874
8 L3014 1877 6 1877
9 L9637 2142 7 2142
10 L3317 2276 8 2276
11 L9018 2418 9 2418
12 L6886 2463 10 2463
13 L5947 2540 11 2540
14 L1107 2681 12 2681
15 L2490 3484 13 3484
16 L7814 3492 14 3492
17 L7761 3506 15 3506
18 L9362 3547 16 3547
19 L1485 3853 17 3853
20 L2169 4434 18 4434
21 L8477 4530 19 4530
22 L2070 4795 20 4795
23 L2070 4795 20 8332
24 L8566 4795 20 8332
25 L8566 4795 20 4795
26 L3295 4948 21 5921
27 L3295 4948 21 4948
28 L9406 4948 21 5921
29 L9406 4948 21 8202
30 L9406 4948 21 4948
31 L2459 5010 22 5010
32 L2247 5368 23 7889
33 L2247 5368 23 5368
34 L6119 5368 23 5368
35 L6119 5368 23 7889
36 L4034 5407 24 5407
37 L6267 6691 26 6899
38 L6267 6691 26 6691
39 L8148 6816 27 6816
40 L3366 6895 28 6895
41 L9363 6901 30 6901
42 L6544 7108 31 7108
43 L6417 7117 32 7117
44 L1682 7253 33 7253
45 L6110 7354 34 7354
46 L6110 7354 34 8411
47 L9254 7354 34 7354
48 L3800 7384 35 7384
49 L4164 7688 36 7688
50 L5756 7792 37 7792
51 L5259 7819 38 7819
52 L8328 8129 40 8129
53 L5224 8156 41 8156
54 L2769 8642 45 8642
55 L5485 9152 46 9152
56 L4063 9220 47 9220
57 L3259 9405 49 9405
58 L5777 9551 50 9551
59 L5924 9614 51 9614
60 L5553 9617 52 9617
61 L3191 9635 53 9635
62 L7291 9683 54 9683
63 L9633 9735 55 9735Mapping credit cards and loyalty card to same person
1 employee can carries multiple credit cards and loyalty cards# A tibble: 1,496 x 8
# Groups: last4ccnum [55]
ccPerson weekday date time location price last4ccnum
<int> <ord> <date> <chr> <chr> <dbl> <chr>
1 1 Mon 2014-01-06 08:16 Brew've Been S… 15.0 1286
2 1 Mon 2014-01-06 12:00 Jack's Magical… 18.6 9241
3 1 Mon 2014-01-06 13:27 Abila Zacharo 50.1 1286
4 1 Mon 2014-01-06 19:50 Frydos Autosup… 274. 9241
5 1 Mon 2014-01-06 07:54 Brew've Been S… 11.9 1286
6 1 Tue 2014-01-07 12:00 Jack's Magical… 18.8 9241
7 1 Tue 2014-01-07 13:24 Kalami Kafenion 45.0 1286
8 1 Tue 2014-01-07 20:15 Ouzeri Elian 11.4 9241
9 1 Wed 2014-01-08 08:16 Brew've Been S… 7.26 1286
10 1 Wed 2014-01-08 12:00 Jack's Magical… 18.8 9241
# … with 1,486 more rows, and 1 more variable: loyaltynum.x <chr> loyaltynum last4ccnum.x ccPerson last4ccnum.y
1 L1107 2681 12 2681
2 L1485 3853 17 3853
3 L1682 7253 33 7253
4 L2070 8332 43 8332
5 L2070 4795 20 4795
6 L2169 4434 18 4434
7 L2247 5368 23 5368
8 L2247 7889 39 7889
9 L2343 8202 42 8202
10 L2459 5010 22 5010
11 L2490 3484 13 3484
12 L2769 8642 45 8642
13 L3014 1877 6 1877
14 L3191 9635 53 9635
15 L3259 9405 49 9405
16 L3288 1286 1 1286
17 L3288 9241 48 9241
18 L3295 5921 25 5921
19 L3295 4948 21 4948
20 L3317 2276 8 2276
21 L3366 6895 28 6895
22 L3572 1286 1 1286
23 L3800 7384 35 7384
24 L4034 5407 24 5407
25 L4063 9220 47 9220
26 L4149 1321 3 1321
27 L4164 7688 36 7688
28 L4424 1874 5 1874
29 L5224 8156 41 8156
30 L5259 7819 38 7819
31 L5485 9152 46 9152
32 L5553 9617 52 9617
33 L5756 7792 37 7792
34 L5777 9551 50 9551
35 L5924 9614 51 9614
36 L5947 2540 11 2540
37 L6110 8411 44 8411
38 L6110 7354 34 7354
39 L6119 7889 39 7889
40 L6119 5368 23 5368
41 L6267 6691 26 6691
42 L6267 6899 29 6899
43 L6417 7117 32 7117
44 L6544 7108 31 7108
45 L6886 2463 10 2463
46 L7291 9683 54 9683
47 L7761 3506 15 3506
48 L7783 1415 4 1415
49 L7814 3492 14 3492
50 L8012 1310 2 1310
51 L8148 6816 27 6816
52 L8328 8129 40 8129
53 L8477 4530 19 4530
54 L8566 4795 20 4795
55 L8566 8332 43 8332
56 L9018 2418 9 2418
57 L9254 7354 34 7354
58 L9362 3547 16 3547
59 L9363 6901 30 6901
60 L9406 8202 42 8202
61 L9406 4948 21 4948
62 L9406 5921 25 5921
63 L9633 9735 55 9735
64 L9637 2142 7 2142Swap
Importing GPS
- Converting Timestamp to “Year-Month-Day Hour:Minutes” (YYYY-MM-DD HH:MM) format
- Separate Timestamp into 2 new columns (Date & Time)
- Converting data type for id to factor
gps <- read_csv("MC2/gps.csv") %>%
mutate(date = as.Date(mdy_hms(Timestamp)), time = format(mdy_hms(Timestamp), "%H:%M"))
gps$Timestamp <- date_time_parse(gps$Timestamp, zone = "", format = "%m/%d/%Y %H:%M:%S")
gps$hr <- strftime(gps$Timestamp, "%H")
gps$id <- as_factor(gps$id)
gps$weekday = wday(gps$date, label = TRUE, abbr = TRUE)
gps
# A tibble: 685,169 x 8
Timestamp id lat long date time hr
<dttm> <fct> <dbl> <dbl> <date> <chr> <chr>
1 2014-01-06 06:28:01 35 36.1 24.9 2014-01-06 06:28 06
2 2014-01-06 06:28:01 35 36.1 24.9 2014-01-06 06:28 06
3 2014-01-06 06:28:03 35 36.1 24.9 2014-01-06 06:28 06
4 2014-01-06 06:28:05 35 36.1 24.9 2014-01-06 06:28 06
5 2014-01-06 06:28:06 35 36.1 24.9 2014-01-06 06:28 06
6 2014-01-06 06:28:07 35 36.1 24.9 2014-01-06 06:28 06
7 2014-01-06 06:28:09 35 36.1 24.9 2014-01-06 06:28 06
8 2014-01-06 06:28:10 35 36.1 24.9 2014-01-06 06:28 06
9 2014-01-06 06:28:11 35 36.1 24.9 2014-01-06 06:28 06
10 2014-01-06 06:28:12 35 36.1 24.9 2014-01-06 06:28 06
# … with 685,159 more rows, and 1 more variable: weekday <ord>Car coordinates when vehicle stopped
I am eliminating coordinates that indicating that the car is moving The GPS car coordinates are recorded every 1-5 secs. Therefore, if there is a GPS record difference of more than 5 min, which means the employee has driven the car to a destination. Thus this eliminates possible traffic light stops and car moving in motion data.
For each employee: 1. I am getting the first and last car coordinate each day 2. Getting places of interest through the day
ts <- gps %>%
group_by(id) %>%
arrange(date, time, by_group=TRUE) %>%
mutate(diff = round(c(difftime(tail(Timestamp, -1), head(Timestamp, -1), units = "mins"), 0)), 2) %>%
mutate(count = 1:n(), FIRST = count == 1, LAST = count == max(count)) %>%
filter(diff > 5 | FIRST == TRUE | LAST == TRUE) %>%
arrange(id) %>%
select(id, lat, long, date, time, diff, hr, weekday)
ts
# A tibble: 3,133 x 8
# Groups: id [40]
id lat long date time diff hr weekday
<fct> <dbl> <dbl> <date> <chr> <drtn> <chr> <ord>
1 1 36.1 24.9 2014-01-06 07:20 0 mins 07 Mon
2 1 36.1 24.9 2014-01-06 07:22 35 mins 07 Mon
3 1 36.0 24.9 2014-01-06 08:04 253 mins 08 Mon
4 1 36.1 24.9 2014-01-06 12:26 59 mins 12 Mon
5 1 36.0 24.9 2014-01-06 13:34 250 mins 13 Mon
6 1 36.1 24.9 2014-01-06 17:48 108 mins 17 Mon
7 1 36.1 24.9 2014-01-06 19:42 7 mins 19 Mon
8 1 36.1 24.9 2014-01-06 19:49 38 mins 19 Mon
9 1 36.1 24.9 2014-01-06 20:33 98 mins 20 Mon
10 1 36.0 24.9 2014-01-06 22:15 46 mins 22 Mon
# … with 3,123 more rowsCompare GPS to credit card
# A tibble: 3,777 x 14
# Groups: id [37]
id lat long date time.x diff hr weekday.x time.y
<fct> <dbl> <dbl> <date> <chr> <drtn> <chr> <ord> <chr>
1 1 36.1 24.9 2014-01-06 07:20 0 mins 07 Mon 07:27
2 1 36.1 24.9 2014-01-06 07:20 0 mins 07 Mon 07:31
3 1 36.1 24.9 2014-01-06 07:20 0 mins 07 Mon 07:33
4 1 36.1 24.9 2014-01-06 07:20 0 mins 07 Mon 07:35
5 1 36.1 24.9 2014-01-06 07:20 0 mins 07 Mon 07:37
6 1 36.1 24.9 2014-01-06 07:20 0 mins 07 Mon 07:41
7 1 36.1 24.9 2014-01-06 07:20 0 mins 07 Mon 07:45
8 1 36.1 24.9 2014-01-06 07:20 0 mins 07 Mon 07:46
9 1 36.1 24.9 2014-01-06 07:20 0 mins 07 Mon 07:46
10 1 36.1 24.9 2014-01-06 07:20 0 mins 07 Mon 07:47
# … with 3,767 more rows, and 5 more variables: location <chr>,
# price <dbl>, last4ccnum <chr>, loyaltynum <chr>, weekday.y <ord>class : RasterLayer
band : 1 (of 3 bands)
dimensions : 1618, 2716, 4394488 (nrow, ncol, ncell)
resolution : 3.148491e-05, 3.148491e-05 (x, y)
extent : 24.82429, 24.9098, 36.04476, 36.0957 (xmin, xmax, ymin, ymax)
crs : +proj=longlat +datum=WGS84 +no_defs
source : /Users/yuntinghong/Documents/SMU/ISSS608 - Visual Analytics/hongyunting/YTBlog_ISSS608/_posts/2021-07-04-yt-assignment-mc2/MC2/Geospatial/MC2-tourist_modified.tif
names : MC2.tourist_modified
values : 0, 255 (min, max)
OGR data source with driver: ESRI Shapefile
Source: "/Users/yuntinghong/Documents/SMU/ISSS608 - Visual Analytics/hongyunting/YTBlog_ISSS608/_posts/2021-07-04-yt-assignment-mc2/MC2/Geospatial", layer: "Abila"
with 3290 features
It has 9 fields
Integer64 fields read as strings: TLID class : SpatialLinesDataFrame
features : 3290
extent : 24.82401, 24.90997, 36.04501, 36.09492 (xmin, xmax, ymin, ymax)
crs : +proj=longlat +datum=WGS84 +no_defs
variables : 9
names : TLID, FEDIRP, FENAME, FETYPE, FEDIRS, FRADDL, TOADDL, FRADDR, TOADDR
min values : 184619449, N, Acera, Ave, N, 1, 20, 1, 17
max values : 184714869, S, Zefirou, Way, N, 7400, 7498, 7401, 7499 Map view of Abila, Kronos’s route and Employees’ whereabout
tmap_mode("view")
tm_shape(bgmap) +
tm_rgb(bgmap, r = 1, g = 2, b = 3, # setting red to band 1, green to band 2, blue to band 3
alpha = NA,
saturation = 1,
interpolate = TRUE,
max.value = 255) +
tmap_options(max.categories = 44) +
tm_shape(Abila_st) +
tm_lines() +
tm_shape(gps_sf_path) +
tm_dots(col ="id",
popup.vars=c("Date:"="date", "Time:"="time", "Week:"="weekday"))
Storing location coordinates
id <- c("1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31","32","33","34","35")
location <- c("Brew've Been Served","Hallowed Grounds","Brew've Been Served","Coffee Cameleon","Abila Airport","Kronos Pipe and Irrigation","Nationwide Refinery","Maximum Iron and Steel","Stewart and Sons Fabrication","Carlyle Chemical Inc.","Coffee Shack","Bean There Done That","Brewed Awakenings","Jack's Magical Beans","Katerinaís CafÈ","Hippokampos","Abila Zacharo","Gelatogalore","Kalami Kafenion","Ouzeri Elian","Guy's Gyros","U-Pump","Frydos Autosupply n' More","Albert's Fine Clothing","Shoppers' Delight","Abila Scrapyard","Frank's Fuel","Chostus Hotel","General Grocer","Kronos Mart","Octavio's Office Supplies","Roberts and Sons","Ahaggo Museum","Desafio Golf Course","Daily Dealz", "GAStech")
#lat <- c(23, 41, 32, 58, 26)
#long <-
desc <- c("Calixto Nils's house","Azada Lars's house","Balas Felix's house","Barranco Ingrid's house","Baza Isak's house","Bergen Linnea's house","Orilla Elsa's house","Alcazar Lucas's house","Cazar Gustav's house","Campo-Corrente Ada's house","Calzas Axel's house","Cocinaro Hideki's house","Ferro Inga's house","Dedos Lidelse's house","Bodrogi Loreto's house","Vann Isia's house","Flecha Sven's house","Frente Birgitta's house","Frente Vira's house","Fusil Stenig's house","Osvaldo Hennie's house","Nubarron Adra's house","Lagos Varja's house","Mies Minke's house","Herrero Kanon's house","Onda Marin's house","Orilla Kare's house","Borrasca Isande's house","Ovan Bertrand's house","Resumir Felix's house","Sanjorge Jr. Sten's house","Strum Orhan's house","Tempestad Brand's house","Vann Edvard's house","Vasco-Pais Willem's house")
df <- data.frame(id, desc)
df
id desc
1 1 Calixto Nils's house
2 2 Azada Lars's house
3 3 Balas Felix's house
4 4 Barranco Ingrid's house
5 5 Baza Isak's house
6 6 Bergen Linnea's house
7 7 Orilla Elsa's house
8 8 Alcazar Lucas's house
9 9 Cazar Gustav's house
10 10 Campo-Corrente Ada's house
11 11 Calzas Axel's house
12 12 Cocinaro Hideki's house
13 13 Ferro Inga's house
14 14 Dedos Lidelse's house
15 15 Bodrogi Loreto's house
16 16 Vann Isia's house
17 17 Flecha Sven's house
18 18 Frente Birgitta's house
19 19 Frente Vira's house
20 20 Fusil Stenig's house
21 21 Osvaldo Hennie's house
22 22 Nubarron Adra's house
23 23 Lagos Varja's house
24 24 Mies Minke's house
25 25 Herrero Kanon's house
26 26 Onda Marin's house
27 27 Orilla Kare's house
28 28 Borrasca Isande's house
29 29 Ovan Bertrand's house
30 30 Resumir Felix's house
31 31 Sanjorge Jr. Sten's house
32 32 Strum Orhan's house
33 33 Tempestad Brand's house
34 34 Vann Edvard's house
35 35 Vasco-Pais Willem's house